package com.easymall.dao.impl;

import com.easymall.dao.CategoryDao;
import com.easymall.domain.Category;
import com.easymall.util.JDBCUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class CategoryDaoImplMariaDB implements CategoryDao {
    @Override
    public void init() {

    }

    @Override
    public Category getCategoryById(int id) throws SQLException {
        String sql = "SELECT id, name, description FROM category WHERE id=?";

        Category category = null;
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try{
            connection = JDBCUtils.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, id);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                category = new Category();
                category.setId(id);
                category.setName(resultSet.getString("name"));
                category.setDescription(resultSet.getString("description"));
            }
            return category;
        } catch (SQLException e) {
            e.printStackTrace();
            throw e;
        } finally {
            JDBCUtils.close(resultSet, preparedStatement, connection);
        }

    }

    @Override
    public Category getCategoryByName(String name) throws SQLException {
        String sql = "SELECT id, name, description FROM category WHERE name=?";

        Category category = null;
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try{
            connection = JDBCUtils.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, name);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                category = new Category();
                category.setId(resultSet.getInt("id"));
                category.setName(name);
                category.setDescription(resultSet.getString("description"));
            }
            return category;
        } catch (SQLException e) {
            e.printStackTrace();
            throw e;
        } finally {
            JDBCUtils.close(resultSet, preparedStatement, connection);
        }
    }

    @Override
    public List<Category> listCategoryAll() throws SQLException {
        String sql = "SELECT id, name, description FROM category";

        List<Category> categorys = null;
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try{
            connection = JDBCUtils.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                if (null == categorys){
                    categorys = new ArrayList<>();
                }
                Category category = new Category();
                category.setId(resultSet.getInt("id"));
                category.setName(resultSet.getString("name"));
                category.setDescription(resultSet.getString("description"));
                categorys.add(category);
            }
            return categorys;
        } catch (SQLException e) {
            e.printStackTrace();
            throw e;
        } finally {
            JDBCUtils.close(resultSet, preparedStatement, connection);
        }
    }

    @Override
    public boolean saveCategory(Category category) throws SQLException {
        String sql = "INSERT INTO category (name, description)VALUES(?,?)";

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try{
            connection = JDBCUtils.getConnection();
            preparedStatement = connection.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
            preparedStatement.setString(1, category.getName());
            preparedStatement.setString(2, category.getDescription());
            int result = preparedStatement.executeUpdate();
            if (1 == result) {
                resultSet = preparedStatement.getGeneratedKeys();
                if (resultSet.next()) {
                    category.setId(resultSet.getInt(1));
                    return true;
                }
            }
            return false;
        } catch (SQLException e) {
            e.printStackTrace();
            throw e;
        } finally {
            JDBCUtils.close(resultSet, preparedStatement, connection);
        }
    }

    @Override
    public boolean deleteCategoryById(int cid) throws SQLException {
        String sql = "DELETE FROM category WHERE id=?";

        Connection connection = null;
        PreparedStatement preparedStatement = null;

        try{
            connection = JDBCUtils.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, cid);
            int result = preparedStatement.executeUpdate();
            if (1 == result) {
                return true;
            }
            return false;
        } catch (SQLException e) {
            e.printStackTrace();
            throw e;
        } finally {
            JDBCUtils.close(null, preparedStatement, connection);
        }
    }
}
